import pandas as pd
import time
import numpy as np
from common_func_defs import *
from datetime import datetime, timedelta, date
from initdatabase import *
import os

df = pd.read_excel("C:/Users/简皓/Desktop/品相/upload.xlsx", sheet_name=None, header=0)

'''—————————————————————————————方法：根据上传内容按stattime刷新数据—————————————————————————————'''
def upload_nostattime_insert(upload_df: pd.DataFrame,table_zh_name, class_name):
    # 得到处理后的上传表（中文列名转英文列名）
    df=upload_field_corr(upload_df, table_zh_name)

    # 将处理后的上传表存入数据库（按stat_time更新数据）
    # 上传结果info
    info='success'

    # 上传的数据一定有得stat_time列，格式一定为date
    if 'stat_time' not in df.columns:
        info='上传失败：请检查数据表中字段是否符合模板要求（缺少数据统计时间列）'
        return info
    try:
        df['stat_time'] = pd.to_datetime(df['stat_time'])
    except:
        info='上传失败：请检查数据表中字段是否符合模板要求（数据统计时间列不符合日期格式要求）'
        return info

    try:
        #连接数据库
        session = get_session()

        # 检测想插入的数据的stat_time是否在数据库里面有东西，有就删了,由此达到刷新数据的效果
        df['stat_time'] = df['stat_time'].astype(str)
        stat_time_ls = list(df["stat_time"].drop_duplicates())
        session.query(class_name).filter(class_name.stat_time.in_(stat_time_ls)).delete()
        # 将数据按stat_time排序从早到晚排序
        df = df.sort_values(by='stat_time')
        # 添加上传该条数据记录的时间和插入该条数据记录的时间列
        df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
        df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
        # 转换数据为字典
        df = df.to_dict(orient='records')
        # 批量插入
        objects = [class_name(**eachline) for eachline in df]
        session.add_all(objects)
        session.commit()
        # 关闭会话
        session.close()
    except:
        info='上传失败：请检查数据表中字段是否符合模板要求'
    return info

'''—————————————————————————————方法：表全量更新—————————————————————————————'''
def upload_allupdate_insert(upload_df: pd.DataFrame,table_zh_name, class_name):
    # 上传结果info
    info = 'success'
    try:
        # 得到处理后的上传表（中文列名转英文列名）
        df = upload_field_corr(upload_df, table_zh_name)

        #连接数据库
        session = get_session()

        # 添加上传该条数据记录的时间和插入该条数据记录的时间列
        df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
        df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
        # 转换数据为字典
        df = df.to_dict(orient='records')

        # 删除原有数据
        session.query(class_name).delete()

        # 批量插入
        objects = [class_name(**eachline) for eachline in df]
        session.add_all(objects)
        print('开始commit')
        start_time = time.time()  # 记录程序开始运行时间
        session.commit()
        end_time = time.time()  # 记录程序结束运行时间
        print('此次commit ：cost %f second' % (end_time - start_time))

        # 关闭会话
        print('commit结束')
        session.close()
    except:
        info = '上传失败：请检查数据表中字段是否符合模板要求'
        print(info)
    return info

'''—————————————————————————————方法：删除df列名的空格—————————————————————————————'''
def delete_col_name_blank(df0: pd.DataFrame, col_name_selected: list):
    df0.rename(columns=lambda x: x.replace(' ', ''), inplace=True)
    df0 = df0[col_name_selected]
    return df0

'''—————————————————————————————方法：上传品相、计划等联合表格—————————————————————————————'''
def pinxiang_jihua_upload(df):
    info = 'success'
    try:
        df_customer_info = df['客户信息表'] #产品明细上传表
        col_name_selected = ['客户编号', '店铺', '性质']

        df_customer_info = delete_col_name_blank(df_customer_info, col_name_selected)
        # 客户信息表
        table_zh_name = '客户信息表'
        df_customer_info.fillna('-', inplace=True)
        upload_allupdate_insert(df_customer_info, table_zh_name, customer_information)

        # 产品信息表
        df_product_information = df['产品信息表'] #产品明细上传表
        col_name_selected = ['物料编码', '品牌', '大系列', '子系列']

        df_product_information = delete_col_name_blank(df_product_information, col_name_selected)
        table_zh_name = '产品信息表'
        df_product_information.fillna('-', inplace=True)
        upload_allupdate_insert(df_product_information, table_zh_name, product_information)

        # 计划月表
        df_plan_month = df['计划月表'] #产品明细上传表
        col_name_selected = [['数据统计时间', '客户编码', '店铺名称', '平台', '性质', '品牌', '计划发货量']]

        df_plan_month = delete_col_name_blank(df_plan_month, col_name_selected)
        table_zh_name = '计划月表'
        upload_allupdate_insert(df_plan_month, table_zh_name, plan_month)

        # 发货销售拆包月表
        df_deliver_sale_bag_month = df['发货销售拆包月表'] #产品明细上传表
        col_name_selected = ['数据统计时间', '物料编码', '客户编号', '本期发货金额', '开单价金额']

        df_deliver_sale_bag_month = delete_col_name_blank(df_deliver_sale_bag_month, col_name_selected)
        table_zh_name = '发货销售拆包月表'
        upload_nostattime_insert(df_deliver_sale_bag_month, table_zh_name, deliver_sale_bag_month)

        # 发货销售月表
        df_deliver_sale_month = df['发货销售月表'] #产品明细上传表
        col_name_selected = ['数据统计时间', '物料编码', '客户编号', '本期发货金额', 'A价金额']

        df_deliver_sale_month = delete_col_name_blank(df_deliver_sale_month, col_name_selected)
        table_zh_name = '发货销售月表'
        upload_nostattime_insert(df_deliver_sale_month, table_zh_name, deliver_sale_month)

    except:
        info = '上传失败：请检查数据表中字段是否符合模板要求'

    return info